<html>
<!-- =====================================================================

  File:      usp_CustomerDetail.htm for Adventure Works Cycles Storefront Sample
  Summary:   Self-documentation for application
  Date:	     June 16, 2003

=====================================================================

  This file is part of the Microsoft SQL Server Code Samples.
  Copyright (C) Microsoft Corporation.  All rights reserved.

This source code is intended only as a supplement to Microsoft
Development Tools and/or on-line documentation.  See these other
materials for detailed information regarding Microsoft code samples.

THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY
KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
PARTICULAR PURPOSE.

======================================================= -->
    <head>
        <title>Adventure Works Cycles Store Documentation</title>
        <link rel="stylesheet" href="style.css">
    </head>
    <body>
        <h1>"usp_CustomerDetail" Stored Procedure</h1>

        <b>Description:</b>

        <P>This stored procedure accepts a CustomerID and returns that customer's name, email address,
        password hash, and password salt. Rather than use a single line recordset,
        usp_CustomerDetail returns the items in individual output parameters. This yields a
        performance increase.</P>

        <b>Definition:</b>

<pre style="background-color:white">
<span style="color: #0000FF"><strong>CREATE</strong></span> Procedure usp_CustomerDetail
<span style="color: #4444FF">(</span>
    @CustomerID <span style="color: #2040a0"><strong>int</strong></span><span style="color: #4444FF">,</span>
    @FirstName   <span style="color: #2040a0"><strong>nvarchar</strong></span><span style="color: #4444FF">(</span>50<span style="color: #4444FF">)</span> OUTPUT<span style="color: #4444FF">,</span>
    @LastName   <span style="color: #2040a0"><strong>nvarchar</strong></span><span style="color: #4444FF">(</span>50<span style="color: #4444FF">)</span> OUTPUT<span style="color: #4444FF">,</span>
    @Email      <span style="color: #2040a0"><strong>nvarchar</strong></span><span style="color: #4444FF">(</span>50<span style="color: #4444FF">)</span> OUTPUT<span style="color: #4444FF">,</span>
    @PasswordHash   <span style="color: #2040a0"><strong>varchar</strong></span><span style="color: #4444FF">(</span>40<span style="color: #4444FF">)</span> OUTPUT<span style="color: #4444FF">,</span>
	@PasswordSalt	<span style="color: #2040a0"><strong>varchar</strong></span><span style="color: #4444FF">(</span>10<span style="color: #4444FF">)</span> OUTPUT<span style="color: #4444FF">,</span>
	@EmailPromotion <span style="color: #2040a0"><strong>int</strong></span> OUTPUT

<span style="color: #4444FF">)</span>
<span style="color: #0000FF"><strong>AS</strong></span>

<span style="color: #0000FF"><strong>SELECT</strong></span> 
    @FirstName <span style="color: #4444FF">=</span> FirstName<span style="color: #4444FF">,</span> 
    @LastName <span style="color: #4444FF">=</span> LastName<span style="color: #4444FF">,</span>
    @Email    <span style="color: #4444FF">=</span> EmailAddress<span style="color: #4444FF">,</span> 
    @PasswordHash <span style="color: #4444FF">=</span> PasswordHash<span style="color: #4444FF">,</span>
	@PasswordSalt <span style="color: #4444FF">=</span> PasswordSalt<span style="color: #4444FF">,</span>
	@EmailPromotion <span style="color: #4444FF">=</span> EmailPromotion

<span style="color: #0000FF"><strong>FROM</strong></span> 
    Person.Contact <span style="color: #0000FF"><strong>as</strong></span> C
JOIN Sales.Individual <span style="color: #0000FF"><strong>as</strong></span> I
<span style="color: #0000FF"><strong>ON</strong></span> I.ContactID <span style="color: #4444FF">=</span> C.ContactID

<span style="color: #0000FF"><strong>WHERE</strong></span> 
    I.CustomerID <span style="color: #4444FF">=</span> @CustomerID;

</pre>

        <b>Database Tables Used: </b>

        <P><i>Customer:</i> &nbsp;The Customer table keeps track of base customer information in the system.
        The primary key is CustomerID.  It has a one to many relationship with the SalesOrderHeader table.  
        </P>
        
        <P><i>Individual:</i> &nbsp;The Individual table keeps track of retail customers.  It contains the link between
        the Customer table and the Contact table for people who buy products on the web site.
        </P>
        
        <p><i>Contact:</i> &nbsp;The Contact table keeps track of people.  It contains information 
        about the users' name, email address, password hash, and password salt.</p>

        <P>
        <img src="1x1.gif" width=25> <IMG src="../docs/images/Individual-Customer-Contact.gif" width="491" height="571">
        </P>

    </body>
</html>
